
<!DOCTYPE html
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml">
   <head>
      <meta charset="utf-8"></meta>
      <meta name="viewport" content="width=device-width, initial-scale=1.0"></meta>
      <title>7.5.&nbsp;Loading Excel (XLS) - Chapter&nbsp;7.&nbsp;Export / Import</title>
      <link rel="stylesheet" type="text/css" href="../../docbook.css"></link>
      <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.1.0/css/font-awesome.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//fonts.googleapis.com/css?family=Open+Sans:400,300,400italic,600,300italic"></link>
      <link rel="stylesheet" type="text/css" href="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/codemirror.min.css"></link>
      <link rel="stylesheet" type="text/css" href="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/theme/neo.min.css"></link>
      <link rel="stylesheet" type="text/css" href="../../css/chunked-base.css"></link>
      <link rel="stylesheet" type="text/css" href="../../css/extra.css"></link><script src="//code.jquery.com/jquery-1.12.4.js" type="text/javascript"></script><script src="//cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js" type="text/javascript"></script><script src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" type="text/javascript"></script><script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/codemirror.min.js" type="text/javascript"></script><script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/addon/runmode/runmode.min.js" type="text/javascript"></script><script src="//cdnjs.cloudflare.com/ajax/libs/codemirror/5.11.0/mode/cypher/cypher.min.js" type="text/javascript"></script><script src="../../javascript/datatable.js" type="text/javascript"></script><script src="../../javascript/colorize.js" type="text/javascript"></script><script src="../../javascript/tabs-for-chunked.js" type="text/javascript"></script><script src="../../javascript/mp-nav.js" type="text/javascript"></script><script src="../../javascript/versionswitcher.js" type="text/javascript"></script><script src="../../javascript/version.js" type="text/javascript"></script><script src="//s3-eu-west-1.amazonaws.com/alpha.neohq.net/docs/new-manual/assets/search.js" type="text/javascript"></script><meta name="generator" content="DocBook XSL Stylesheets V1.79.1"></meta>
      <link rel="prev" href="../import-csv/" title="7.4.&nbsp;Import CSV"></link>
      <link rel="next" href="../load-xml/" title="7.6.&nbsp;Load XML"></link>
      <link rel="shortcut icon" href="https://neo4j.com/wp-content/themes/neo4jweb/favicon.ico"></link><script>
        $(document).ready(function() {
          CodeMirror.colorize();
          tabTheSource($('body'));
          var $header = $('header').first();
          $header.prepend(
            $('<a href="" id="logo"><img src="https://neo4j.com/wp-content/themes/neo4jweb/assets/images/neo4j-logo-2015.png" alt="Neo4j Logo"></img></a>')
          );
          var $sidebar = $('<div id="sidebar-wrapper"></div>');
          $.get('toc.html', function (d){
            $(d).appendTo($sidebar);
            highlightToc();
            highlightLibraryHeader();
          });
          $sidebar.insertAfter($('header').first());
        });
        </script></head>
   <body>
      <header>
         <div class="searchbox">
            <form id="search-form" class="search" name="search-form" role="search"><input id="search-form-input" name="q" title="search" type="search" lang="en" placeholder="Search Neo4j docs..." aria-label="Search Neo4j documentation" max-length="128" required="required"></input><input id="search-form-button" type="submit" value="Search"></input></form>
         </div>
         <ul class="documentation-library">
            <li><a href="https://neo4j.com/docs/operations-manual/current">Operations Manual</a></li>
            <li><a href="https://neo4j.com/docs/developer-manual/current/">Developer Manual</a></li>
            <li><a href="https://neo4j.com/docs/ogm-manual/current/">OGM Manual</a></li>
            <li><a href="https://neo4j.com/docs/graph-algorithms/current/">Graph Algorithms</a></li>
            <li><a href="https://neo4j-contrib.github.io/neo4j-apoc-procedures/3.5/">APOC</a></li>
            <li><a href="https://neo4j.com/docs/java-reference/current/">Java Reference</a></li>
         </ul>
         <nav id="header-nav"><span class="nav-previous"><a accesskey="p" href="../import-csv/"><span class="fa fa-long-arrow-left" aria-hidden="true"></span>Import CSV</a></span><span class="nav-current">
               <p class="nav-title hidden">7.5.&nbsp;Loading Excel (XLS)</p></span><span class="nav-next"><a accesskey="n" href="../load-xml/">Load XML<span class="fa fa-long-arrow-right" aria-hidden="true"></span></a></span></nav>
      </header>
      <div id="search-results" class="hidden"></div>
      <section class="section" id="load-xls">
         <div class="titlepage">
            <div>
               <div>
                  <h2 class="title" style="clear: both"><a class="anchor" href="#load-xls"></a>7.5.&nbsp;Loading Excel (XLS)
                  </h2>
               </div>
            </div>
         </div>
         <section class="section" id="_library_requirements">
            <div class="titlepage">
               <div>
                  <div>
                     <h3 class="title"><a class="anchor" href="#_library_requirements"></a>7.5.1.&nbsp;Library Requirements
                     </h3>
                  </div>
               </div>
            </div>
            <p>For loading XLS we&#8217;re using the Apache POI library, which works well with old and new Excel formats, but is quite large.
               That&#8217;s why we decided not to include it into the apoc jar, but make it an optional dependency.
            </p>
            <p>Please download these jars and put them into your <code class="literal">plugins</code> directory:
            </p>
            <p>For XLS files:</p>
            <div class="itemizedlist">
               <ul class="itemizedlist" style="list-style-type: disc; ">
                  <li class="listitem"><a class="link" href="http://repo1.maven.org/maven2/org/apache/poi/poi/3.17/poi-3.17.jar" target="_top">poi-3.17.jar</a></li>
               </ul>
            </div>
            <p>Additional for XLSX files:</p>
            <div class="itemizedlist">
               <ul class="itemizedlist" style="list-style-type: disc; ">
                  <li class="listitem"><a class="link" href="http://repo1.maven.org/maven2/org/apache/commons/commons-collections4/4.1/commons-collections4-4.1.jar" target="_top">commons-collections4-4.1.jar</a></li>
                  <li class="listitem"><a class="link" href="http://repo1.maven.org/maven2/org/apache/poi/poi-ooxml/3.17/poi-ooxml-3.17.jar" target="_top">poi-ooxml-3.17.jar</a></li>
                  <li class="listitem"><a class="link" href="http://repo1.maven.org/maven2/org/apache/poi/poi-ooxml-schemas/3.17/poi-ooxml-schemas-3.17.jar" target="_top">poi-ooxml-schemas-3.17.jar</a></li>
                  <li class="listitem"><a class="link" href="http://repo1.maven.org/maven2/org/apache/xmlbeans/xmlbeans/2.6.0/xmlbeans-2.6.0.jar" target="_top">xmlbeans-2.6.0.jar</a></li>
                  <li class="listitem"><a class="link" href="http://repo1.maven.org/maven2/com/github/virtuald/curvesapi/1.04/curvesapi-1.04.jar" target="_top">curvesapi-1.04.jar</a></li>
               </ul>
            </div>
         </section>
         <section class="section" id="_usage_2">
            <div class="titlepage">
               <div>
                  <div>
                     <h3 class="title"><a class="anchor" href="#_usage_2"></a>7.5.2.&nbsp;Usage
                     </h3>
                  </div>
               </div>
            </div>
            <p>The usage of <code class="literal">apoc.load.xls</code> is similar to apoc.load.csv with the main difference the ability to select a worksheet or a range from a sheet to load.
            </p>
            <p>You can either select the sheet by name like <code class="literal">'Kids'</code>, or offset like <code class="literal">'Results!B2:F3'</code></p>
            <p><code class="literal">CALL apoc.load.xls({url}, {Name of sheet}, {config})</code></p>
            <p>The <code class="literal">{config}</code> parameter is a map
            </p>
            <div class="informaltable">
               <div class="table" id="d0e6954">
                  <table class="informaltable" border="1">
                     <colgroup>
                        <col class="col_1"></col>
                        <col class="col_2"></col>
                     </colgroup>
                     <thead>
                        <tr>
                           <th style="text-align: left; vertical-align: top; ">name</th>
                           <th style="text-align: left; vertical-align: top; ">description</th>
                        </tr>
                     </thead>
                     <tbody>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">mapping</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">{mapping:{'&lt;sheet&gt;':{type:'&lt;type&gt;', dateFormat: '&lt;format&gt;', dateParse: [&lt;formats&gt;]}}}</code></p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">&lt;sheet&gt;</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">name of the sheet</code></p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">&lt;type&gt;</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">Default <code class="literal">String</code>, The type of the conversion requested (<code class="literal">STRING</code>, <code class="literal">INTEGER</code>, <code class="literal">FLOAT</code>, <code class="literal">BOOLEAN</code>, <code class="literal">NULL</code>, <code class="literal">LIST</code>, <code class="literal">DATE</code>, <code class="literal">DATE_TIME</code>, <code class="literal">LOCAL_DATE</code>, <code class="literal">LOCAL_DATE_TIME</code>, <code class="literal">LOCAL_TIME</code>, <code class="literal">TIME</code>)</code></p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">dateFormat: &lt;format&gt;</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">Convert the Date into String (only String is allowed)</code></p>
                           </td>
                        </tr>
                        <tr>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">dateParse: [&lt;formats&gt;]</code></p>
                           </td>
                           <td style="text-align: left; vertical-align: top; ">
                              <p><code class="literal">Convert the String into Date (Array of strings are allowed)</code></p>
                           </td>
                        </tr>
                     </tbody>
                  </table>
               </div>
            </div>
            <section class="section" id="_note">
               <div class="titlepage">
                  <div>
                     <div>
                        <h4 class="title"><a class="anchor" href="#_note"></a>7.5.2.1.&nbsp;Note
                        </h4>
                     </div>
                  </div>
               </div>
               <p>In dateParse the first format matched return the date formatted, otherwise it will return an error</p>
               <p>In <code class="literal">format</code> config you can use the pattern describe as the Temporal functions: temporal funcionts
               </p>
            </section>
         </section>
         <section class="section" id="_examples_for_apoc_load_xls">
            <div class="titlepage">
               <div>
                  <div>
                     <h3 class="title"><a class="anchor" href="#_examples_for_apoc_load_xls"></a>7.5.3.&nbsp;Examples for apoc.load.xls
                     </h3>
                  </div>
               </div>
            </div><pre class="programlisting highlight"><code data-lang="cypher">CALL apoc.load.xls('file:///path/to/file.xls','Full',{mapping:{Integer:{type:'int'}, Array:{type:'int',array:true,arraySep:';'}}})</code></pre><div class="informalfigure">
               <div class="mediaobject"><img src="https://raw.githubusercontent.com/neo4j-contrib/neo4j-apoc-procedures/3.4/docs/images/apoc.load.xls.png" alt="apoc.load.xls"></img></div>
            </div><pre class="programlisting highlight"><code data-lang="cypher">CALL apoc.load.xls('http://bit.ly/2nXgHA2','Kids')</code></pre><p>Some examples with type/dateFormat and dateParse:</p><pre class="programlisting highlight"><code data-lang="cypher">CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String'}}})</code></pre><div class="figure" id="d0e7079">
               <div class="figure-title">Figure&nbsp;7.1.&nbsp;results</div>
               <div class="figure-contents">
                  <div class="mediaobject"><img src="https://raw.githubusercontent.com/neo4j-contrib/neo4j-apoc-procedures/3.4/docs/images/apoc.load.xls_1.png" alt="apoc.load.xls 1"></img></div>
               </div>
            </div><pre class="programlisting highlight"><code data-lang="cypher">CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String',dateFormat:'iso_date'}}})</code></pre><div class="figure" id="d0e7090">
               <div class="figure-title">Figure&nbsp;7.2.&nbsp;results</div>
               <div class="figure-contents">
                  <div class="mediaobject"><img src="https://raw.githubusercontent.com/neo4j-contrib/neo4j-apoc-procedures/3.4/docs/images/apoc.load.xls_2.png" alt="apoc.load.xls 2"></img></div>
               </div>
            </div><pre class="programlisting highlight"><code data-lang="cypher">CALL apoc.load.xls('test_date.xlsx','sheet',{mapping:{Date:{type:'String',dateParse:["wrongPath", "dd-MM-yyyy", "dd/MM/yyyy", "yyyy/MM/dd", "yyyy/dd/MM", "yyyy-dd-MM'T'hh:mm:ss"]}}})</code></pre><div class="figure" id="d0e7101">
               <div class="figure-title">Figure&nbsp;7.3.&nbsp;results</div>
               <div class="figure-contents">
                  <div class="mediaobject"><img src="https://raw.githubusercontent.com/neo4j-contrib/neo4j-apoc-procedures/3.4/docs/images/apoc.load.xls_3.png" alt="apoc.load.xls 3"></img></div>
               </div>
            </div>
         </section>
      </section>
      <footer><script type="text/javascript">
          (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
            (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
          m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
          })(window,document,'script','//www.google-analytics.com/analytics.js','ga');
          //Allow Linker
          ga('create', 'UA-1192232-34','auto', {'allowLinker': true});
          ga('send', 'pageview');
          // Load the plugin.
          ga('require', 'linker');
          // Define which domains to autoLink.
          ga('linker:autoLink', ['neo4j.org','neo4j.com','neotechnology.com','graphdatabases.com','graphconnect.com']);
        </script><script type="text/javascript">
          document.write(unescape("%3Cscript src='//munchkin.marketo.net/munchkin.js' type='text/javascript'%3E%3C/script%3E"));
        </script><script>Munchkin.init('773-GON-065');</script></footer>
   </body>
</html>